﻿using Microsoft.Data.Sqlite;
using Newtonsoft.Json;

namespace MyTinnyApi.util
{
    public class DbUtil
	{
        private static SqliteConnection conn;

        public static void Initialize()
        {
            var connectionString = $"Data Source={AppConfig.DbPath}";
            conn = new SqliteConnection(connectionString);
            conn.Open();
        }

        public static SqliteTransaction BeginTransaction()
        {
            return conn.BeginTransaction();
        }

        public static int ExecuteNonQuery(string sql, params SqliteParameter[] parameters)
        {
            using (var command = conn.CreateCommand())
            {
                command.CommandText = sql;
                if (parameters != null && parameters.Length > 0)
                {
                    command.Parameters.AddRange(parameters);
                }
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 执行插入操作，并返回插入后自动生成的id
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns>插入后自动生成的id</returns>
        public static int ExecuteInsert(string sql, params SqliteParameter[] parameters)
        {
            using (var command = conn.CreateCommand())
            {
                command.CommandText = sql;
                if (parameters != null && parameters.Length > 0)
                {
                    command.Parameters.AddRange(parameters);
                }
                command.ExecuteNonQuery();

                command.CommandText = "SELECT LAST_INSERT_ROWID()";
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        /// <summary>
        /// 返回指定类型的Dictionary格式结果
        /// </summary>
        /// <typeparam name="TKey"></typeparam>
        /// <typeparam name="TValue"></typeparam>
        /// <param name="sql"></param>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static Dictionary<TKey, TValue> ExecutorReaderForDic<TKey, TValue>(string sql, string key, string value)
        {
            var dic = new Dictionary<TKey, TValue>();
            var list = ExecuteReader(sql);
            if (list?.Count > 0)
            {
                foreach (var d in list)
                {
                    dic[ConvertToType<TKey>(d[key].ToString())] = ConvertToType<TValue>(d[value]);
                }
            }
            return dic;
        }

        private static T ConvertToType<T>(object value)
        {
            return (T)Convert.ChangeType(value, typeof(T));
        }

        public static List<T> ExecuteReaderForType<T>(string sql)
        {
            var list = ExecuteReader(sql);
            return list.Select(d =>
            {
                string json = JsonConvert.SerializeObject(d);
                T item = JsonConvert.DeserializeObject<T>(json);
                return item;
            }).ToList();
        }



        /// <summary>
        /// 返回Dictionary类型的List结果
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<Dictionary<string, object>> ExecuteReader(string sql)
        {
            using (var command = conn.CreateCommand())
            {
                command.CommandText = sql;
                //if (parameters != null && parameters.Length > 0)
                //{
                //    command.Parameters.AddRange(parameters);
                //}
                var reader = command.ExecuteReader();

                var resultList = new List<Dictionary<string, object>>();

                while (reader.Read())
                {
                    var row = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string columnName = reader.GetName(i);
                        object columnValue = reader.GetValue(i);
                        row[columnName] = columnValue;
                    }

                    resultList.Add(row);
                }

                return resultList;
            }
        }
    }
}

